package com.hnkywl.sts.dao.zjgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.zjgl.Yhsx;
import com.hnkywl.sts.entity.zjgl.Yhsxmx;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-4-24
 * Time: 下午8:36
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class YhsxDao extends SimpleHibernateTemplate<Yhsx, Long> {
    public Page<Yhsx> findYhsxList(Long Ids,String gsMc,Date jkrqStart,Date jkrqEnd,String yhMc,Integer zt,int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Yhsx> page = new Page<Yhsx>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (Ids != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.id = ? ");
            param.add(Ids);
        }
        if (Ids == null && gsMc!=null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.jc like ? ");
            param.add("%" + gsMc.trim() + "%");
        }
        if (jkrqStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" jkrq >= ?");
            param.add(jkrqStart);
        }
        if (jkrqEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" jkrq <= ?");
            param.add(jkrqEnd);
        }

        if (yhMc!=null && !yhMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" khh.qc like ? ");
            param.add("%" + yhMc.trim() + "%");
        }
        if (zt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" zt = ? ");
            param.add(zt);
        }
        //==============记录数量===============
        hql = "select count(*) from Yhsx where 1=1" + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by zt,jkrq";
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Yhsx where 1=1"  + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by zt,jkrq";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    //银行授信还款明细
    public List<Map> findYhsxHkmx(Yhsx yhsx) {
        Criteria criteria = getSession().createCriteria(Yhsxmx.class)
                .createAlias("yhsx", "yhsx", CriteriaSpecification.LEFT_JOIN)
                .createAlias("zffs", "zffs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("yhsx.khh", "khh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("yhsx.nbgs", "nbgs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("hkje").as("hkje"))
                        .add(Property.forName("hkrq").as("hkrq"))
                        .add(Property.forName("hxje").as("hxje"))
                        .add(Property.forName("zffs.id").as("zffs.id"))
                        .add(Property.forName("zffs.z").as("zffs.z"))
                        .add(Property.forName("pjhm").as("pjhm"))
                        .add(Property.forName("bz").as("bz"))
                        .add(Property.forName("khh.id").as("khh.id"))
                        .add(Property.forName("khh.qc").as("khh.qc"))
                        .add(Property.forName("nbgs.id").as("nbgs.id"))
                        .add(Property.forName("nbgs.qc").as("nbgs.qc"))
                        .add(Property.forName("yhsx.jkrq").as("yhsx.jkrq"))
                ).add(Restrictions.eq("yhsx.id", yhsx.getId()))
                 .add(Restrictions.eq("yhsxsflx", "fk"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> findSumYhsx(Long gsId, String khhMc,Date jkrqStart, Date jkrqEnd, Integer zt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = " select sum(t.jkje) as \"jkje\",sum(t.lxe) as \"lxe\",sum(t.ljhk) as \"ljhk\" from t_biz_yhsx t " +
            " left join t_sys_gsjg g on g.id = t.nbgs_id " +
            " left join t_sys_khh k on k.id = t.khh_id ";

        if (gsId != null) {
            condition.add(" g.id=:gs_id ");
            param.put("gs_id", gsId);
        }
        if (khhMc != null) {
            condition.add(" k.qc like '%"+khhMc+"%' ");
        }
        if (jkrqStart != null) {
            condition.add(" t.jkrq>= :jkrq ");
            param.put("jkrq", jkrqStart);
        }

        if (jkrqEnd != null) {
            condition.add(" t.jkrq=< :jkrq ");
            param.put("jkrq", jkrqEnd);
        }

        if(zt != null){
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }

        String dataRegion = RegionDao.createSQLDataRegion(null, "t");
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql += " where t.deleted!=1 and " + whereSql + dataRegion;

        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }
}
